SET NOCOUNT ON
GO
USE [VNPT_Bsc]
GO
if(object_id('GetAll','P')is not null)
	drop proc GetAll
go
create proc [dbo].[GetAll]
	@tableName nvarchar(128),
	@orderBy nvarchar(512)
AS
	declare @sql nvarchar(1000)
	set @sql='select * from '+@tableName+' '+@orderBy+''
	exec (@sql)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_fact_costs_revenues') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_fact_costs_revenues
GO
CREATE PROCEDURE InsertUpdateDelete_fact_costs_revenues

	@branch_code nchar(10) = null ,
	@cost_code varchar(50) = null ,
	@timekey int = null ,
	@cost float = null ,
	@unit_code varchar(10) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into fact_costs_revenues values( @branch_code, @cost_code,@unit_code, @timekey, @cost)
	end
else if @Action=1
	begin
		update fact_costs_revenues set cost_code=@cost_code,timekey=@timekey,cost=@cost where branch_code=@branch_code and cost_code=@cost_code and unit_code=@unit_code
	end
else if @Action=2
	begin
		delete fact_costs_revenues where branch_code=@branch_code
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_dim_unit') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_dim_unit
GO
CREATE PROCEDURE InsertUpdateDelete_dim_unit

	@code varchar(10) ,
	@name nvarchar(100) = null ,
	@slug varchar(10) ,
	@Action int
AS

if @Action=0
	begin
		insert into dim_unit values( @code, @name, @slug)
	end
else if @Action=1
	begin
		update dim_unit set name=@name,slug=@slug where code=@code
	end
else if @Action=2
	begin
		delete dim_unit where code=@code
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_fact_costs_revenues_target') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_fact_costs_revenues_target
GO
CREATE PROCEDURE InsertUpdateDelete_fact_costs_revenues_target

	@cost_code varchar(10) ,
	@unit nchar(10) = null ,
	@timekey int = null ,
	@target float = null ,
	@branch_code varchar(10) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into fact_costs_revenues_target values( @cost_code, @unit, @timekey, @target, @branch_code)
	end
else if @Action=1
	begin
		update fact_costs_revenues_target set unit=@unit,timekey=@timekey,target=@target,branch_code=@branch_code where cost_code=@cost_code
	end
else if @Action=2
	begin
		delete fact_costs_revenues_target where cost_code=@cost_code
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_dim_costs_revenues') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_dim_costs_revenues
GO
CREATE PROCEDURE InsertUpdateDelete_dim_costs_revenues

	@code varchar(50) ,
	@name nvarchar(200) = null ,
	@unit_code varchar(10) ,
	@code2 nvarchar(100) = null ,
	@code3 nvarchar(100) = null ,
	@code4 nchar(10) = null ,
	@code5 nchar(10) = null ,
	@parent_code varchar(50) = null ,
	@note nvarchar(1000) = null ,
	@slug varchar(500) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into dim_costs_revenues values( @code, @name, @unit_code, @code2, @code3, @code4, @code5, @parent_code, @note, @slug)
	end
else if @Action=1
	begin
		update dim_costs_revenues set name=@name,unit_code=@unit_code,code2=@code2,code3=@code3,code4=@code4,code5=@code5,parent_code=@parent_code,note=@note,slug=@slug where code=@code
	end
else if @Action=2
	begin
		delete dim_costs_revenues where code=@code
	end
RETURN
GO
---------------------------------------------------------------------
if OBJECT_ID('spGetCostByKey','P') is not null
	drop proc spGetCostByKey
go
create proc spGetCostByKey @branch_code varchar(10)=null,@cost_code varchar(50)=null,@timekey int=null,@unit_code varchar(10)=null
as
	DECLARE  @query varchar(max)
	DECLARE  @where varchar(max)
	set @query='SELECT * FROM dbo.fact_costs_revenues'
	set @where=''
	if(@branch_code is not null)
	begin
		if(@where<>'') set @where= @where + ' AND '
		set @where=@where+'branch_code='''+@branch_code+''''
	end
	if(@cost_code is not null)
	begin
		if(@where<>'') set @where= @where + ' AND '
		set @where=@where+'cost_code='''+@cost_code+''''
	end
	if(@timekey is not null)
	begin
		if(@where<>'') set @where= @where + ' AND '
		set @where=@where+'timekey='+cast(@timekey as varchar(10))
	end
	if(@unit_code is not null)
	begin
		if(@where<>'') set @where= @where + ' AND '
		set @where=@where+'unit_code='''+@unit_code+''''
	end
	if(@where<>'')
		set @query=@query+' where '+@where	
	--print @query
	exec (@query)